import pandas as pd
from PIL import Image
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Alignment


def createAddress(path, lists):
    data = pd.DataFrame(lists,
                        columns=['内部订单号', '订单类型', '线上订单号', '店铺', '订单状态', "快递公司", "快递单号",
                                 "发货仓", "包裹状态", "标签", "最新物流时间", "最新物流信息", "异常跟进标签",
                                 "平台付款时间", "计划发货时间", "系统发货时间", "线上同步发货时间",
                                 "支付-发货耗时(小时)", "备注", "截图地址", "金额", "收货地址"])
    data['内部订单号'] = data['内部订单号'].astype(str)
    data['线上订单号'] = data['线上订单号'].astype(str)
    data['快递单号'] = data['快递单号'].astype(str)
    data.to_excel(path, index=False)


def List(path):
    df = pd.read_excel(path,
                       usecols=['内部订单号', '订单类型', '线上订单号', '店铺', '订单状态', "快递公司", "快递单号",
                                "发货仓", "包裹状态", "标签", "最新物流时间", "最新物流信息", "异常跟进标签",
                                "平台付款时间", "计划发货时间", "系统发货时间", "线上同步发货时间",
                                "支付-发货耗时(小时)", "备注"])
    df = df[~df['快递公司'].str.contains("义乌邮政|圆通")]
    dataList = df.values.tolist()
    return dataList


def excel插入图片(filepath):
    # 打开Excel文件
    wb = load_workbook(filepath)
    # 获取默认的工作表
    ws = wb.active
    ws.column_dimensions['A'].width = 33  # 设置A列的宽度为20
    ws.column_dimensions['B'].width = 33  # 设置B列的宽度为15
    ws.column_dimensions['D'].width = 33  # 设置B列的宽度为15
    # 循环每一行
    for row in ws.iter_rows(min_row=2):
        row[3].alignment = Alignment(horizontal='right')  # 右对齐
        # 获取图片路径
        img_path = row[2].value
        # 如果图片路径不为空，则插入图片
        if img_path:
            # 加载图片
            img = Image(img_path)
            # 设置图片大小
            img.width = 100
            img.height = 100
            # 在当前行的第四列插入图片
            ws.add_image(img, f'D{row[3].row}')
    # 保存Excel文件
    wb.save(filepath)
    # 关闭Excel文件
    wb.close()


def 筛选excel(filepath, name, outpath):
    df = pd.read_excel(filepath, usecols=['快递公司', '快递单号', '截图地址', '金额', '收货地址'])
    if len(name) > 1:
        df = df[df['快递公司'].str.contains(name[0]) & df['快递公司'].str.contains(name[1])]
    else:
        df = df[df['快递公司'].str.contains(name[0])]
    # 如果df为空，则返回空列表
    if df.empty:
        return []
    df.to_excel(outpath, index=False)
    excel插入图片(outpath)
